package com.seqb.utils;

import com.xuxueli.poi.excel.ExcelImportUtil;
import com.xuxueli.poi.excel.annotation.ExcelField;
import com.xuxueli.poi.excel.annotation.ExcelSheet;
import com.xuxueli.poi.excel.util.FieldReflectionUtil;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.ss.usermodel.*;
import org.springframework.util.StringUtils;

import java.io.IOException;
import java.io.InputStream;
import java.lang.reflect.Field;
import java.lang.reflect.Modifier;
import java.math.BigDecimal;
import java.text.SimpleDateFormat;
import java.util.*;

/**
 * @author lilinshen
 * @title Excel导入工具
 * @description 重写ExcelImportUtil类，实现将对应的列，保存在对象对应的属性中（excel没有的不设置保存）
 * @date 2019/11/11
 */
public class ExcelImportUtils extends ExcelImportUtil {

    /**
     * 从Workbook导入Excel文件，并封装成对象
     *
     * @param sheetClass
     * @param workbook
     * @return
     */
    public static List<Object> importExcel(Class<?> sheetClass, Workbook workbook) {
        try {
            // sheet
            ExcelSheet excelSheet = sheetClass.getAnnotation(ExcelSheet.class);
            String sheetName = (excelSheet != null && excelSheet.name() != null && excelSheet.name().trim().length() > 0) ? excelSheet.name().trim() : sheetClass.getSimpleName();

            // sheet field
            List<Field> fields = new ArrayList<Field>();
            if (sheetClass.getDeclaredFields() != null && sheetClass.getDeclaredFields().length > 0) {
                for (Field field : sheetClass.getDeclaredFields()) {
                    if (Modifier.isStatic(field.getModifiers())) {
                        continue;
                    }
                    fields.add(field);
                }
            }

            if (fields == null || fields.size() == 0) {
                throw new RuntimeException(">>>>>>>>>>> xxl-excel error, data field can not be empty.");
            }

            // sheet data
            Sheet sheet = workbook.getSheet(sheetName);

            Iterator<Row> sheetIterator = sheet.rowIterator();
            int rowIndex = 0;
            List<Object> dataList = new ArrayList<Object>();
            List<Object> headList = new ArrayList<Object>();

            while (sheetIterator.hasNext()) {
                Row row = sheetIterator.next();
                // 记录第一行，默认为Header
                if (rowIndex == 0) {
                    Iterator<Cell> cellIterator = row.cellIterator();
                    while (cellIterator.hasNext()) {
                        Cell cell = cellIterator.next();
                        String stringCellValue = cell.getStringCellValue();
                        if (!StringUtils.isEmpty(stringCellValue)) {
                            headList.add(stringCellValue);
                        }
                    }
                }
                if (rowIndex > 0) {
                    Object rowObj = sheetClass.newInstance();
                    for (int i = 0; i < fields.size(); ++i) {
                        Field field = fields.get(i);
                        // 反射出object的属性，是否有@ExcelField注解，没有的不set值
                        ExcelField excelField = field.getAnnotation(ExcelField.class);
                        if (excelField == null) {
                            continue;
                        }
                        // 获取@ExcelField注解的属性name的值，没有的不set值
                        String name = excelField.name().trim();
                        if (StringUtils.isEmpty(name)) {
                            continue;
                        }
                        // 判断@ExcelField注解的属性name的值，在headList中是否有记录
                        int index = headList.indexOf(name);
                        if (index < 0) {
                            continue;
                        }
                        // 设置从excel读取的数据以string格式读取（否则数值的cell会读取报错）
                        row.getCell(index).setCellType(CellType.STRING);
                        String fieldValueStr = row.getCell(index).getStringCellValue();
                        // 将读取的值，根据object的属性的类型，进行转换
                        Object fieldValue = FieldReflectionUtil.parseValue(field, fieldValueStr);

                        field.setAccessible(true);
                        field.set(rowObj, fieldValue);
                    }
                    dataList.add(rowObj);
                }
                rowIndex++;
            }
            return dataList;
        } catch (IllegalAccessException var15) {
            throw new RuntimeException(var15);
        } catch (InstantiationException var16) {
            throw new RuntimeException(var16);
        }
    }

    /**
     * 导入Excel数据流，并封装成对象
     *
     * @param sheetClass
     * @param inputStream
     * @return
     */
    public static List<Object> importExcel(Class<?> sheetClass, InputStream inputStream) {
        try {
            Workbook workbook = WorkbookFactory.create(inputStream);
            List<Object> dataList = importExcel(sheetClass, workbook);
            return dataList;
        } catch (IOException e) {
            throw new RuntimeException(e);
        } catch (InvalidFormatException e) {
            throw new RuntimeException(e);
        }
    }

    /**
     * 导入Excel数据流，并封装成对象（支持导入有错的数据）
     *
     * @param sheetClass
     * @param inputStream
     * @return
     */
    public static List<Object> importExcelWithMistake(Class<?> sheetClass, InputStream inputStream, List<String> mistakeList) {
        try {
            Workbook workbook = WorkbookFactory.create(inputStream);
            return importExcelWithMistake(sheetClass, workbook, mistakeList);
        } catch (Exception e) {
            throw new RuntimeException(e);
        }
    }

    /**
     * 判断该行是否为空，为空返回true，不为空返回false
     *
     * @param row
     * @param size
     * @return
     */
    public static boolean judgeRowEmpty(Row row, int size) {
        boolean result = true;
        for (int i = 0; i < size; i++) {
            if (null != row.getCell(i)) {
                // 设置从excel读取的数据以string格式读取（否则数值的cell会读取报错）
                row.getCell(i).setCellType(CellType.STRING);
                String fieldValueStr = row.getCell(i).getStringCellValue();
                if (!StringUtils.isEmpty(fieldValueStr)) {
                    result = false;
                    break;
                }
            }
        }
        return result;
    }

    /**
     * String类型转BigDecimal类型数据
     *
     * @param value
     * @return
     */
    public static BigDecimal parseBigDecimal(String value) {
        try {
            value = value.replaceAll("　", "");
            return new BigDecimal(value);
        } catch (NumberFormatException var2) {
            throw new RuntimeException("parseBigDecimal but input illegal input=" + value, var2);
        }
    }

    /**
     * 重写FieldReflectionUtil的parseValue方法，增加处理BigDecimal类型数据
     *
     * @param field
     * @param value
     * @return
     */
    public static Object parseValue(Field field, String value) {
        Class<?> fieldType = field.getType();
        if (BigDecimal.class.equals(fieldType)) {
            return parseBigDecimal(value);
        }
        return FieldReflectionUtil.parseValue(field, value);
    }

    /**
     * 从Workbook导入Excel文件，并封装成对象。有错的数据，保存到mistakeList集合中。
     * 主要是（Long，Double，Date类型转换出错）
     *
     * @param sheetClass
     * @param workbook
     * @return
     */
    public static List<Object> importExcelWithMistake(Class<?> sheetClass, Workbook workbook, List<String> mistakeList) {
        int rowIndex = 0;
        try {
            // sheet
            ExcelSheet excelSheet = sheetClass.getAnnotation(ExcelSheet.class);
            String sheetName = (excelSheet != null && excelSheet.name() != null && excelSheet.name().trim().length() > 0) ? excelSheet.name().trim() : sheetClass.getSimpleName();
            // sheet field
            List<Field> fields = new ArrayList<Field>();
            if (sheetClass.getDeclaredFields() != null && sheetClass.getDeclaredFields().length > 0) {
                for (Field field : sheetClass.getDeclaredFields()) {
                    if (Modifier.isStatic(field.getModifiers())) {
                        continue;
                    }
                    fields.add(field);
                }
            }
            if (fields == null || fields.size() == 0) {
                throw new RuntimeException(">>>>>>>>>>> xxl-excel error, data field can not be empty.");
            }

            // sheet data
            Sheet sheet = workbook.getSheet(sheetName);
            // 处理没有设置sheetName的情况，默认取第一个Sheet
            if (null == sheet) {
                sheet = workbook.getSheetAt(0);
            }
            Iterator<Row> sheetIterator = sheet.rowIterator();
            List<Object> dataList = new ArrayList<Object>();
            Map<String, Object> headMap = new HashMap<String, Object>(16);
            while (sheetIterator.hasNext()) {
                Row row = sheetIterator.next();
                // 记录第一行，默认为Header
                if (rowIndex == 0) {
                    Iterator<Cell> cellIterator = row.cellIterator();
                    int headIndex = 0;
                    while (cellIterator.hasNext()) {
                        Cell cell = cellIterator.next();
                        String stringCellValue = cell.getStringCellValue();
                        if (!org.springframework.util.StringUtils.isEmpty(stringCellValue)) {
                            headMap.put(stringCellValue, headIndex);
                        }
                        headIndex++;
                    }
                }
                StringBuilder mistake = new StringBuilder();
                if (rowIndex > 0) {
                    // 判断该行是否为空
                    if (judgeRowEmpty(row, fields.size())) {
                        continue;
                    }
                    Object rowObj = sheetClass.newInstance();
                    boolean isItCorrect = true;
                    for (int i = 0; i < fields.size(); ++i) {
                        Field field = fields.get(i);
                        // 反射出object的属性，是否有@ExcelField注解，没有的不set值
                        ExcelField excelField = field.getAnnotation(ExcelField.class);
                        if (excelField == null) {
                            continue;
                        }
                        // 获取@ExcelField注解的属性name的值，没有的不set值
                        String name = excelField.name().trim();
                        if (org.springframework.util.StringUtils.isEmpty(name)) {
                            continue;
                        }
                        // 判断@ExcelField注解的属性name的值，在headList中是否有记录
                        if (!headMap.containsKey(name)) {
                            continue;
                        }
                        int index = (int) headMap.get(name);
                        Class<?> fieldType = field.getType();
                        String fieldValueStr = null;
                        // 处理日期格式
                        String dateformat = excelField.dateformat();
                        boolean dateFlag = (Date.class.equals(fieldType) || !"yyyy-MM-dd HH:mm:ss".equals(dateformat)) && null != row.getCell(index);
                        if (dateFlag) {
                            try {
                                short dataFormat = row.getCell(index).getCellStyle().getDataFormat();
                                if (dataFormat == 0 || dataFormat == 49) {
                                    isItCorrect = false;
                                    mistake.append('“' + name + '”' + "不是正确的日期； ");
                                    row.getCell(index).setCellType(CellType.STRING);
                                    fieldValueStr = row.getCell(index).getStringCellValue();
                                } else {
                                    Date date = row.getCell(index).getDateCellValue();
                                    if (null != date) {
                                        String datePattern = "yyyy-MM-dd HH:mm:ss";
                                        if (excelField != null) {
                                            datePattern = excelField.dateformat();
                                        }
                                        SimpleDateFormat dateFormat = new SimpleDateFormat(datePattern);
                                        fieldValueStr = dateFormat.format(date);
                                    }
                                }
                            } catch (Exception e) {
                                isItCorrect = false;
                                mistake.append('“' + name + '”' + "不是正确的日期； ");
                                row.getCell(index).setCellType(CellType.STRING);
                                fieldValueStr = row.getCell(index).getStringCellValue();
                            }
                        } else if (null != row.getCell(index)) {
                            // 设置从excel读取的数据以string格式读取（否则数值的cell会读取报错）
                            row.getCell(index).setCellType(CellType.STRING);
                            fieldValueStr = row.getCell(index).getStringCellValue();
                        }

                        // 将读取的值，根据object的属性的类型，进行转换
                        Object fieldValue = null;
                        if (Long.class.equals(fieldType)) {
                            try {
                                fieldValue = Long.valueOf(fieldValueStr);
                            } catch (NumberFormatException var2) {
                                isItCorrect = false;
                                mistake.append('“' + name + '”' + "不是正确的整数； ");
                            }
                        } else if (Double.class.equals(fieldType)) {
                            try {
                                fieldValue = Double.valueOf(fieldValueStr);
                            } catch (Exception e) {
                                isItCorrect = false;
                                mistake.append('“' + name + '”' + "不是正确的数字； ");
                            }
                        } else if (BigDecimal.class.equals(fieldType)) {
                            try {
                                fieldValue = parseBigDecimal(fieldValueStr);
                            } catch (Exception e) {
                                isItCorrect = false;
                                mistake.append('“' + name + '”' + "不是正确的数字； ");
                            }
                        } else {
                            if (isItCorrect) {
                                fieldValue = parseValue(field, fieldValueStr);
                            }
                        }
                        if (isItCorrect) {
                            field.setAccessible(true);
                            field.set(rowObj, fieldValue);
                        }
                    }
                    if (isItCorrect) {
                        dataList.add(rowObj);
                    } else {
                        dataList.add(null);
                    }
                    if (!org.springframework.util.StringUtils.isEmpty(mistake.toString())) {
                        mistakeList.add("在第" + (rowIndex + 1) + "行数据中：" + mistake.toString());
                    }
                }
                rowIndex++;
            }
            return dataList;
        } catch (Exception e) {
            throw new RuntimeException(e);
        }
    }


}
